signon ;		
rsubmit;
libname manasi 	''; 
libname bankrup	'';
libname crime	'';
options compress=YES;

* get dates of birth (for sibings) ;
data sib_dob ;
	set manasi.kids_ssr_full_prep2 (keep = pan dob sex rename=(dob=old_dob)) ;

	n_dob_char = put(old_dob,$8.); 

	n_dobyy = substr(n_dob_char,1,4); 
	n_dobyy = input(n_dobyy,4.0); 
	n_dobmm = substr(n_dob_char,5,2); 
	n_dobmm = input(n_dobmm,2.0); 
	n_dobdd = substr(n_dob_char,7,2);
	n_dobdd = input(n_dobdd,2.0);

	dob = mdy(n_dobmm, n_dobdd, n_dobyy) ; 
	format dob MMDDYY10. ;

run ;

data sib_dob ;
	set 
		manasi.prwora_age18_prep1 (keep = pan birth_jd sex rename=(birth_jd=dob)) 
		sib_dob (keep = pan dob sex) ;
run ;

proc sort data=sib_dob out=sib_dob nodupkey ;
	by pan ;
run;

proc sort data=manasi.prwora_age18 out=crime.prwora_age18_crimesib ;
	by hun;
run;

* Create dataset with only sibling pair SSNs listed, get DOB running variable and 
other relevant characteristics of the older sibling;

data crime.prwora_age18_crimesib ;
	merge
		manasi.allssr_kids_wsiblings_long 
			(
			keep=hun sibssn
			rename=(hun=age18_ssn)
			in=fromsibs1
			)

		crime.prwora_age18_crimesib
			(
			keep=hun toa dob dobdd_stata dobdd_run pdscc
			n_male n_diag1cat dibdig dibmdr first_pay_dte n_firstage
			n_onssipost n_combpaypost age18_final_con adultcdr_term
			rename=(hun=age18_ssn)
			in=fromsumm1
			)
		;
	by age18_ssn;
	if fromsibs1=1 & fromsumm1=1;
run; 

* Merge in younger sibling SSI receipt, earnings, etc;
* NOTE THAT HUN HAS BEEN CHANGED TO REFER TO THE SIBLING, 
NOT THE 18 YEAR OLD, TO MAKE IT EASIER TO MERGE IN EARNINGS DATA;

proc sort data=crime.prwora_age18_crimesib out=crime.prwora_age18_crimesib ;
	by sibssn;
run;

* Merge in sibling DOB and name to link to crime data;
data crime.prwora_age18_crimesib;

	merge
		crime.prwora_age18_crimesib
			(in=from_prworax
			 rename=(sibssn=hun))

		sib_dob
			(rename=(pan=hun dob=sib_dob sex=sib_sex))
		;

		by hun;
		if from_prworax=1;
	run;

data crime.prwora_age18_crimesib ;

	merge
		crime.prwora_age18_crimesib
			(in=from_prworax)

		bankrup.bankrup_ssrformerge 
			(in=from_namex
			rename=(ssn=hun lastn=fssr_lastn firstn=fssr_firstn middlen=fssr_middlen))
		;

		by hun;
		if from_prworax=1;
		from_name=from_namex;
	run;

* See how many merge to name file ;
proc means data=crime.prwora_age18_crimesib ;
	var from_name ;
run ;


* CONVERT DATA SETS TO STATA;
%let DIR=;
%macro stataexport(dsname);
proc export data=crime.&dsname
     file="&DIR\&dsname..dta"
     dbms=DTA REPLACE;
run;
%mend stataexport;

%stataexport(prwora_age18_crimesib);

quit;

endrsubmit;
signoff ;

